<?php


namespace App\Http\Controllers\Admin;


use App\Models\Admin;
use App\Services\StatCompareService;
use App\Services\UserService;
use Illuminate\Http\Request;
use Illuminate\Contracts\Foundation\Application;
use Illuminate\Support\Facades\DB;

class StatCompareController
{

    private $userService;

    private $statCompareService;

    private $request;

    public function __construct(UserService $userService, Request $request, StatCompareService $statCompareService)
    {
        $this->userService = $userService;
        $this->request = $request;
        $this->statCompareService = $statCompareService;
    }

    /**
     * 比较全部卷号数据统计
     *
     * @param Request $request
     * @return Application|\Illuminate\Contracts\View\Factory|\Illuminate\View\View
     */
    public function compareAllRolls(Request $request)
    {
        $year = $request->get('year') ?? date('Y');
        $old_year = $request->get('old_year') ?? date('Y') - 1;
        $dealer_id = $request->get('dealer_id') ?? 0;
        $dist_id = $request->get('dist_id') ?? 0;

        $start_date = $year . '-01';
        $end_date = ($year + 1) . '-01';

        $newData = $this->getAllRollSql($start_date, $end_date, $dealer_id, $dist_id);
        $oldData = $this->getAllRollSql($old_year . '-01', ($old_year + 1) . '-01', $dealer_id, $dist_id);

        $months =  [
            '一月','二月','三月','四月','五月','六月','七月','八月','九月','十月','十一月','十二月'
        ];

        $data = [
            'distributions' => $this->getDistributions(),
            'new_data' => $newData,
            'old_data' => $oldData,
            'page'     => getPage('膜销量对比情况', '数据报表'),
            'year'     => $year,
            'old_year' => $old_year,
            'months'   => $months,
            'dist_id'   => $dist_id,
            'dealer_id' => $dealer_id,
            'dealers'   => $this->getDealers($dist_id),
            'start_year' => date('Y'),
            'title'     => '膜销量对比情况',
        ];

        return view('admin.stat_compare.compare_rolls', $data);
    }

    public function getAllRollSql(string $start_date, string $end_date, int $dealer_id, int $dist_id)
    {
        $sql = 'SELECT sum(sale_1) + sum(sale_2) + sum(sale_3) as sale_sum, 
        sum(transfer_1) + sum(transfer_2) + sum(transfer_3) as transfer_sum,
        sum(return_1) + sum(return_2) + sum(return_3) as return_sum,
        sum(stock_1) + sum(stock_2) + sum(stock_3) as stock_sum, DATE_FORMAT(create_date,"%m") months FROM xpel_roll_stat WHERE create_date>="' . $start_date . '"';
        $sql .= ' AND create_date <"' . $end_date . '"';

        if (!empty($dealer_id)) {
            $sql .= ' AND user_id=' . $dealer_id;
        }
        if (!empty($dist_id)) {
            $dealer_ids = $this->userService->getDealers([$dist_id]);
            if (!empty($dealer_ids)) {
                return ' AND user_id IN (' . implode(',', $dealer_ids)  . ')';
            }
        }
        $sql .= ' GROUP BY months';

        $result = DB::select($sql);

        return $result;
    }

    public function compareQmRolls(Request $request)
    {
        $year = $request->get('year') ?? date('Y');
        $old_year = $request->get('old_year') ?? date('Y') - 1;
        $dealer_id = $request->get('dealer_id') ?? 0;
        $dist_id = $request->get('dist_id') ?? 0;

        $start_date = $year . '-01';
        $end_date = ($year + 1) . '-01';

        $newData = $this->getQmRolls($start_date, $end_date, $dealer_id, $dist_id);
        $oldData = $this->getQmRolls($old_year . '-01', ($old_year + 1) . '-01', $dealer_id, $dist_id);

        $months =  [
            '一月','二月','三月','四月','五月','六月','七月','八月','九月','十月','十一月','十二月'
        ];

        $data = [
            'distributions' => $this->getDistributions(),
            'new_data' => $newData,
            'old_data' => $oldData,
            'page'     => getPage('漆面膜销量对比情况', '数据报表'),
            'year'     => $year,
            'old_year' => $old_year,
            'months'   => $months,
            'dist_id'   => $dist_id,
            'dealer_id' => $dealer_id,
            'dealers'   => $this->getDealers($dist_id),
            'start_year' => date('Y'),
            'title'     => '漆面膜销量对比情况',
        ];
        return view('admin.stat_compare.compare_qm_rolls', $data);
    }

    public function getQmRolls(string $start_date, string $end_date, int $dealer_id, int $dist_id)
    {
        $sql = 'SELECT sum(sale_1) as sale_sum, 
        sum(transfer_1) as transfer_sum,
        sum(return_1) as return_sum,
        DATE_FORMAT(create_date,"%m") months FROM xpel_roll_stat WHERE create_date>="' . $start_date . '"';
        $sql .= ' AND create_date <"' . $end_date . '"';

        if (!empty($dealer_id)) {
            $sql .= ' AND user_id=' . $dealer_id;
        }
        if (!empty($dist_id)) {
            $sql .= ' AND user_id=' . $dist_id;
        }
        if (empty($dealer_id) && empty($dist_id)) {
            $sql .= ' AND user_id=1';
        }
        $sql .= ' GROUP BY months';

        $result = DB::select($sql);

        return $result;
    }

    public function compareWinRolls(Request $request)
    {
        $year = $request->get('year') ?? date('Y');
        $old_year = $request->get('old_year') ?? date('Y') - 1;
        $dealer_id = $request->get('dealer_id') ?? 0;
        $dist_id = $request->get('dist_id') ?? 0;

        $start_date = $year . '-01';
        $end_date = ($year + 1) . '-01';

        $newData = $this->getWinRolls($start_date, $end_date, $dealer_id, $dist_id);
        $oldData = $this->getWinRolls($old_year . '-01', ($old_year + 1) . '-01', $dealer_id, $dist_id);

        $months =  [
            '一月','二月','三月','四月','五月','六月','七月','八月','九月','十月','十一月','十二月'
        ];

        $data = [
            'distributions' => $this->getDistributions(),
            'new_data' => $newData,
            'old_data' => $oldData,
            'page'     => getPage('窗面膜销量对比情况', '数据报表'),
            'year'     => $year,
            'old_year' => $old_year,
            'months'   => $months,
            'dist_id'   => $dist_id,
            'dealer_id' => $dealer_id,
            'dealers'   => $this->getDealers($dist_id),
            'start_year' => date('Y'),
            'title'     => '窗面膜销量对比情况',
        ];
        return view('admin.stat_compare.compare_win_rolls', $data);
    }

    public function getWinRolls(string $start_date, string $end_date, int $dealer_id, int $dist_id)
    {
        $sql = 'SELECT sum(sale_2) as sale_sum, 
        sum(transfer_2) as transfer_sum,
        sum(return_2) as return_sum,
        DATE_FORMAT(create_date,"%m") months FROM xpel_roll_stat WHERE create_date>="' . $start_date . '"';
        $sql .= ' AND create_date <"' . $end_date . '"';

        if (!empty($dealer_id)) {
            $sql .= ' AND user_id=' . $dealer_id;
        }
        if (!empty($dist_id)) {
            $sql .= ' AND user_id=' . $dist_id;
        }
        if (empty($dealer_id) && empty($dist_id)) {
            $sql .= ' AND user_id=1';
        }
        $sql .= ' GROUP BY months';

        $result = DB::select($sql);

        return $result;
    }

    /**
     * 获取经销商
     *
     * @return array
     */
    public function getDistributions()
    {
        $distributions = Admin::where('status', 1)
            ->where('user_type', 2)
            ->orderBy('status')
            ->orderBy('unique_id')
            ->select(['id', 'abbr'])
            ->get();

        return $distributions ?? [];
    }

    public function getDealers(int $distId)
    {
        if (empty($distId)) {
            return [];
        }

        $dealers = Admin::where('status', 1)
            ->where('user_type', 3)
            ->where('creator_id', $distId)
            ->orderBy('status')
            ->orderBy('unique_id')
            ->select(['id', 'abbr'])
            ->get();

        return $dealers ?? [];
    }

    public function compareWarrantyTotal(Request $request)
    {
        $year = $request->get('year') ?? date('Y');
        $old_year = $request->get('old_year') ?? date('Y') - 1;
        $dealer_id = $request->get('dealer_id') ?? 0;
        $dist_id = $request->get('dist_id') ?? 0;
        $real = $request->get('real') ?? 1;

        $start_date = $year . '-01';
        $end_date = ($year + 1) . '-01';

        if ($real == 1) {
            $sql = 'SELECT count(distinct(w.id)) as total, fm.product_type_id, installation_date
            FROM warranty AS w 
            LEFT JOIN users AS u ON w.user_id=u.id
            LEFT JOIN warranty_to_vehicle_coverage as wvc ON wvc.warranty_id=w.id
            LEFT JOIN film_type_master as fm ON fm.id=wvc.film_type_id
            WHERE installation_date>="%s"
            AND installation_date<="%s"
            AND approved=1
            AND wvc.deleted_at IS NULL
            AND w.deleted_at IS NULL
        ';
            $sql = sprintf($sql, $start_date, $end_date);
            if (!empty($dealer_id)) {
                $sql .= ' AND u.id=' . $dealer_id;
            }
            if (!empty($dist_id)) {
                $dealer_ids = $this->userService->getDealers([$dist_id]);
                if (!empty($dealer_ids)) {
                    $dealer_ids[] = $dist_id;
                    $sql .= ' AND u.id IN(' . implode(',', $dealer_ids) . ')';
                }
            }

            $sql .= ' GROUP BY fm.product_type_id ORDER BY installation_date ASC';
            $result = DB::select($sql);
            $newData = [];
            foreach ($result as $item) {
                $month = date('Y-m', strtotime($item->installation_date));
                if (!isset($newData[$month])) {
                    $newData[$month]['roll_1_total'] = $item->product_type_id == 1 ? $item->total : 0;
                    $newData[$month]['roll_2_total'] = $item->product_type_id == 2 ? $item->total : 0;
                    $newData[$month]['months'] = $month;
                } else {
                    $newData[$month]['roll_1_total'] += $item->product_type_id == 1 ? $item->total : 0;
                    $newData[$month]['roll_2_total'] += $item->product_type_id == 2 ? $item->total : 0;
                }
            }
            $newData = array_values($newData);
            $sql = 'SELECT count(distinct(w.id)) as total, fm.product_type_id, installation_date
            FROM warranty AS w 
            LEFT JOIN users AS u ON w.user_id=u.id
            LEFT JOIN warranty_to_vehicle_coverage as wvc ON wvc.warranty_id=w.id
            LEFT JOIN film_type_master as fm ON fm.id=wvc.film_type_id
            WHERE installation_date>="%s"
            AND installation_date<="%s"
            AND approved=1
            AND wvc.deleted_at IS NULL
            AND w.deleted_at IS NULL
        ';
            $sql = sprintf($sql, $old_year . '-01', ($old_year + 1) . '-01');
            if (!empty($dealer_id)) {
                $sql .= ' AND u.id=' . $dealer_id;
            }
            if (!empty($dist_id)) {
                $dealer_ids = $this->userService->getDealers([$dist_id]);
                if (!empty($dealer_ids)) {
                    $dealer_ids[] = $dist_id;
                    $sql .= ' AND u.id IN(' . implode(',', $dealer_ids) . ')';
                }
            }


            $sql .= ' ORDER BY installation_date ASC';
            $oldResult = DB::select($sql);

            $oldData = [];
            foreach ($oldResult as $item) {
                $month = date('Y-m', strtotime($item->installation_date));
                if (!isset($newData[$month])) {
                    $oldData[$month]['roll_1_total'] = $item->product_type_id == 1 ? $item->total : 0;
                    $oldData[$month]['roll_2_total'] = $item->product_type_id == 2 ? $item->total : 0;
                    $oldData[$month]['months'] = $month;
                } else {
                    $oldData[$month]['roll_1_total'] += $item->product_type_id == 1 ? $item->total : 0;
                    $oldData[$month]['roll_2_total'] += $item->product_type_id == 2 ? $item->total : 0;
                }
            }
        } else {
            $newData = $this->getWarrantyTotalSql($start_date, $end_date, $dist_id, $dealer_id);
            $oldData = $this->getWarrantyTotalSql($old_year . '-01', ($old_year + 1) . '-01', $dist_id, $dealer_id);
        }


        $months =  [
            '一月','二月','三月','四月','五月','六月','七月','八月','九月','十月','十一月','十二月'
        ];

        $data = [
            'distributions' => $this->getDistributions(),
            'new_data' => $newData,
            'old_data' => $oldData,
            'page'     => getPage('膜销量对比情况', '数据报表'),
            'year'     => $year,
            'old_year' => $old_year,
            'months'   => $months,
            'dist_id'   => $dist_id,
            'dealer_id' => $dealer_id,
            'dealers'   => $this->getDealers($dist_id),
            'start_year' => date('Y'),
            'title'     => '质保数量统计',
        ];

        return view('admin.stat_compare.compare_warranty_total', $data);
    }

    public function compareWarrantyDetail(Request $request)
    {
        $year = $request->get('year') ?? date('Y');
        $old_year = $request->get('old_year') ?? date('Y') - 1;
        $dealer_id = $request->get('dealer_id') ?? 0;
        $dist_id = $request->get('dist_id') ?? 0;
        $real = $request->get('real') ?? 1;

        $start_date = $year . '-01';
        $end_date = ($year + 1) . '-01';

        $sql = 'SELECT count(distinct(w.id)) as total, fm.product_type_id, date_format(approved_date, "%m") as months
        FROM warranty AS w 
        LEFT JOIN users AS u ON w.user_id=u.id
        LEFT JOIN warranty_to_vehicle_coverage as wvc ON wvc.warranty_id=w.id
        LEFT JOIN film_type_master as fm ON fm.id=wvc.film_type_id
        WHERE approved_date>="' . $start_date . '"
        AND approved_date<="' . $end_date . '" AND approved=1 AND w.deleted_at IS NULL  AND wvc.deleted_at IS NULL ';
        if (!empty($dealer_id)) {
            $sql .= ' AND u.id=' . $dealer_id;
        }
        if (!empty($dist_id)) {
            $sql .= ' AND distributor_id=' . $dist_id;
        }

        $sql .= ' GROUP BY months, fm.product_type_id ORDER BY approved_date ASC';
        $result = DB::select($sql);
        $newData = [];
        foreach ($result as $item) {
            if (strtotime($item->months) > time()) {
                continue;
            }
            $month = $item->months;
            if (!isset($newData[$month])) {
                $newData[$month]['roll_1_total'] = $item->product_type_id == 1 ? $item->total : 0;
                $newData[$month]['roll_2_total'] = $item->product_type_id == 2 ? $item->total : 0;
                $newData[$month]['months'] = $month;
            } else {
                $newData[$month]['roll_1_total'] += $item->product_type_id == 1 ? $item->total : 0;
                $newData[$month]['roll_2_total'] += $item->product_type_id == 2 ? $item->total : 0;
            }
        }
        //$newData = array_values($newData);
        $sql = 'SELECT count(distinct(w.id)) as total, fm.product_type_id, date_format(approved_date, "%m") as months
        FROM warranty AS w 
        LEFT JOIN users AS u ON w.user_id=u.id
        LEFT JOIN warranty_to_vehicle_coverage as wvc ON wvc.warranty_id=w.id
        LEFT JOIN film_type_master as fm ON fm.id=wvc.film_type_id
        WHERE approved_date>="' . $old_year . '-01' . '"
        AND approved_date<="' . (($old_year + 1) . '-01') . '" AND approved=1 AND w.deleted_at IS NULL AND wvc.deleted_at IS NULL';
        if (!empty($dealer_id)) {
            $sql .= ' AND u.id=' . $dealer_id;
        }
        if (!empty($dist_id)) {
            $sql .= ' AND distributor_id=' . $dist_id;
        }

        $sql .= ' GROUP BY months, fm.product_type_id ORDER BY approved_date ASC';
        $oldResult = DB::select($sql);
        $oldData = [];
        foreach ($oldResult as $item) {
            if (strtotime($item->months) > time()) {
                continue;
            }
            $month = $item->months;
            if (!isset($oldData[$month])) {
                $oldData[$month]['roll_1_total'] = $item->product_type_id == 1 ? $item->total : 0;
                $oldData[$month]['roll_2_total'] = $item->product_type_id == 2 ? $item->total : 0;
                $oldData[$month]['months'] = $month;
            } else {
                $oldData[$month]['roll_1_total'] += $item->product_type_id == 1 ? $item->total : 0;
                $oldData[$month]['roll_2_total'] += $item->product_type_id == 2 ? $item->total : 0;
            }
        }

        //$oldData = array_values($oldData);
        $months =  [
            '01' => '一月',
            '02' => '二月',
            '03' => '三月',
            '04' => '四月',
            '05' => '五月',
            '06' => '六月',
            '07' => '七月',
            '08' => '八月',
            '09' => '九月',
            '10' => '十月',
            '11' => '十一月',
            '12' => '十二月'
        ];

        $data = [
            'distributions' => $this->getDistributions(),
            'new_data' => $newData,
            'old_data' => $oldData,
            'page'     => getPage('膜销量对比情况', '数据报表'),
            'year'     => $year,
            'old_year' => $old_year,
            'months'   => $months,
            'dist_id'   => $dist_id,
            'dealer_id' => $dealer_id,
            'dealers'   => $this->getDealers($dist_id),
            'start_year' => date('Y'),
            'title'     => '质保数量统计',
        ];

        return view('admin.stat_compare.compare_warranty_detail', $data);
    }

    public function getWarrantyTotalSql(string $start_date, string $end_date, int $dist_id, int $dealer_id)
    {
        $sql = 'SELECT sum(roll_1) as roll_1_total, sum(roll_2) as roll_2_total, DATE_FORMAT(create_date,"%m") months FROM xpel_warranty_stat WHERE create_date>="' . $start_date . '" AND create_date<"' . $end_date . '" ';

        if (!empty($dealer_id)) {
            $sql .= ' AND dealer_id=' . $dealer_id;
        }
        if (!empty($dist_id)) {
            $dealer_ids = $this->userService->getDealers([$dist_id]);
            if (!empty($dealer_ids)) {
                $dealer_ids[] = $dist_id;
                $sql .= ' AND dealer_id IN (' . implode(',', $dealer_ids)  . ')';
            }
        }

        $sql .= ' GROUP BY months';

        $result = DB::select($sql);

        return $result;
    }

    public function compareWarrantyPart(Request $request)
    {
        $year = $request->get('year') ?? date('Y');
        $old_year = $request->get('old_year') ?? date('Y') - 1;
        $dealer_id = $request->get('dealer_id') ?? 0;
        $dist_id = $request->get('dist_id') ?? 0;

        $start_date = $year . '-01';
        $end_date = ($year + 1) . '-01';

        $newData = $this->getWarrantyPartSql($start_date, $end_date, $dist_id, $dealer_id);
        $oldData = $this->getWarrantyPartSql($old_year . '-01', ($old_year + 1) . '-01', $dist_id, $dealer_id);

        $months =  [
            '一月','二月','三月','四月','五月','六月','七月','八月','九月','十月','十一月','十二月'
        ];

        $data = [
            'distributions' => $this->getDistributions(),
            'new_data' => $newData,
            'old_data' => $oldData,
            'page'     => getPage('局部件与全车件对比', '数据报表'),
            'year'     => $year,
            'old_year' => $old_year,
            'months'   => $months,
            'dist_id'   => $dist_id,
            'dealer_id' => $dealer_id,
            'dealers'   => $this->getDealers($dist_id),
            'start_year' => date('Y'),
            'title'     => '局部件与全车件对比',
        ];

        return view('admin.stat_compare.compare_warranty_part', $data);
    }

    public function getWarrantyPartSql(string $start_date, string $end_date, int $dist_id, int $dealer_id)
    {
        $sql = 'SELECT sum(part) as roll_1_total, sum(full) as roll_2_total, DATE_FORMAT(create_date,"%m") months FROM xpel_warranty_stat WHERE create_date>="' . $start_date . '" AND create_date<"' . $end_date . '" ';

        if (!empty($dealer_id)) {
            $sql .= ' AND dealer_id=' . $dealer_id;
        }
        if (!empty($dist_id)) {
            $dealer_ids = $this->userService->getDealers([$dist_id]);
            if (!empty($dealer_ids)) {
                $dealer_ids[] = $dist_id;
                $sql .= ' AND dealer_id IN (' . implode(',', $dealer_ids)  . ')';
            }
        }

        $sql .= ' GROUP BY months';

        $result = DB::select($sql);

        return $result;
    }

    public function compareWarrantyOrder(Request $request)
    {
        $year = $request->get('year') ?? date('Y');
        $old_year = $request->get('old_year') ?? date('Y') - 1;
        $dealer_id = $request->get('dealer_id') ?? 0;
        $dist_id = $request->get('dist_id') ?? 0;
        $type = $request->get('type') ?? 1;

        $start_date = $year . '-01';
        $end_date = ($year + 1) . '-01';

        $newData = $this->getWarrantyOrderSql($start_date, $end_date,  $dist_id, $dealer_id, $type);
        $oldData = $this->getWarrantyOrderSql($old_year . '-01', ($old_year + 1) . '-01', $dist_id, $dealer_id, $type);

        $months =  [
           '01' => '一月',
           '02' => '二月',
           '03' => '三月',
           '04' => '四月',
           '05' => '五月',
            '06' => '六月',
            '07' => '七月',
            '08' => '八月',
            '09' => '九月',
            '10' => '十月',
            '11' => '十一月',
            '12' => '十二月'
        ];

        $data = [
            'distributions' => $this->getDistributions(),
            'new_data' => $newData,
            'old_data' => $oldData,
            'page'     => getPage('质保数量与订购产品卷数对比', '数据报表'),
            'year'     => $year,
            'old_year' => $old_year,
            'months'   => $months,
            'dist_id'   => $dist_id,
            'dealer_id' => $dealer_id,
            'dealers'   => $this->getDealers($dist_id),
            'start_year' => date('Y'),
            'title'     => '质保数量与订购产品卷数对比',
        ];

        return view('admin.stat_compare.compare_warranty_order', $data);
    }

    public function getWarrantyOrderSql(string $start_date, string $end_date, int $dist_id, int $dealer_id, $type = 1)
    {
        $sql = 'SELECT SUM(num) as total,  date_format(create_date, "%m") as months
        FROM xpel_warranty_roll_detail_stat AS w 
        LEFT JOIN users AS u ON w.dealer_id=u.id
        WHERE create_date>="' . $start_date . '"
        AND w.product_type_id=' . $type . '
        AND create_date<="' . $end_date . '"';
        if (!empty($dealer_id)) {
            $sql .= ' AND u.id=' . $dealer_id;
        }
        if (!empty($dist_id)) {
            $sql .= ' AND w.dist_id=' . $dist_id;
        }

        $sql .= ' GROUP BY months';
        $result = DB::select($sql);

        $total = [];
        foreach ($result as $item) {
            if (strtotime($item->months) > time()) {
                continue;
            }
            $month = $item->months;
            if (!isset($total[$month])) {
                $total[$month]['warranty_num'] = $item->total;
                $total[$month]['order_num'] = 0;
            } else {
                $total[$month]['warranty_num'] += $item->total;
            }
        }

        $sql = 'SELECT SUM(num) AS total, date_format(create_month, "%m") AS months FROM xpel_dealer_roll_stat WHERE create_month>="' . $start_date . '" AND create_month<="' . $end_date . '" AND product_type_id=' . $type;
        if (!empty($dealer_id)) {
            $sql .= ' AND dealer_id=' . $dealer_id;
        }
        if (!empty($dist_id)) {
            $sql .= ' AND dealer_id=' . $dist_id;
        }
        if (empty($dealer_id) && empty($dist_id)) {
            $sql .= ' AND dealer_id=1';
        }
        $sql .= ' GROUP BY months';
        $result = DB::select($sql);
        foreach ($result as $item) {
            if (strtotime($item->months) > time()) {
                continue;
            }
            $month = $item->months;
            if (!isset($total[$month])) {
                $total[$month]['order_num'] = $item->total;
                $total[$month]['warranty_num'] = 0;
            } else {
                $total[$month]['order_num'] += $item->total;
            }
        }
        return $total;
    }

    public function compareBrand(Request $request)
    {
        $year = $request->get('year') ?? date('Y');
        $old_year = $request->get('old_year') ?? date('Y') - 1;
        $dealer_id = $request->get('dealer_id') ?? 0;
        $dist_id = $request->get('dist_id') ?? 0;
        $month = $request->get('month') ?? '01';

        $start_date = $year . '-' . $month;
        $end_date = ($year + 1) . '-' . $month;

        $brands = '';
        $newData = $this->getBrandSql($start_date, $end_date, $brands, $dist_id, $dealer_id);
        if (!empty($newData)) {
            foreach ($newData as $newDatum) {
                $brands .= "'" . $newDatum->car_brand . "',";
            }
            $brands = rtrim($brands, ',');
        }
        $oldData = $this->getBrandSql($old_year . '-' . $month, ($old_year + 1) . '-' . $month, $brands, $dist_id, $dealer_id);

        $months =  [
            '一月','二月','三月','四月','五月','六月','七月','八月','九月','十月','十一月','十二月'
        ];

        $result_1 = [];
        $brandsArr = [];
        foreach ($newData as $item) {
            $result_1[$item->car_brand] = $item->brand_count;
            $brandsArr[] = $item->car_brand;
        }

        $result_2 = [];
        foreach ($oldData as $item) {
            $result_2[$item->car_brand] = $item->brand_count;
        }

        $data = [
            'distributions' => $this->getDistributions(),
            'new_data'  => $result_1,
            'old_data'  => $result_2,
            'page'      => getPage('车辆品牌排名', '数据报表'),
            'year'      => $year,
            'old_year'  => $old_year,
            'months'    => $months,
            'dist_id'   => $dist_id,
            'dealer_id' => $dealer_id,
            'brands'    => $brandsArr,
            'dealers'   => $this->getDealers($dist_id),
            'start_year' => date('Y'),
            'title'     => '车辆品牌排名',
        ];

        return view('admin.stat_compare.compare_brand', $data);
    }

    public function getBrandSql(string $start_date, string $end_date, string $brands, int $dist_id, int $dealer_id)
    {
        $sql = 'SELECT count(1) AS brand_count, car_brand FROM xpel_car_brand_stat WHERE create_date>="' . $start_date . '"';
        $sql .= ' AND create_date <"' . $end_date . '"';

        if (!empty($dealer_id)) {
            $sql .= ' AND dealer_id=' . $dealer_id;
        }

        if (!empty($dist_id)) {
            $dealer_ids = $this->userService->getDealers([$dist_id]);
            if (!empty($dealer_ids)) {
                $dealer_ids[] = $dist_id;
                $sql .= ' AND dealer_id IN (' . implode(',', $dealer_ids)  . ')';
            }
        }

        if (!empty($brands)) {
            $sql .= ' AND car_brand IN (' . $brands . ')';
        }

        $sql .= ' GROUP BY car_brand ORDER BY brand_count DESC';

        $result = DB::select($sql);

        return $result;
    }

    public function totalSaleRoll(Request $request)
    {
        $type = $request->input('type');

        $start_date = date('Y') . '-01';
        $end_date = date('Y-m-d');
        $sql = 'SELECT SUM(num) AS total, film_name as simplified_chiness_value, product_type_id FROM xpel_dealer_roll_stat WHERE create_month>="%s" AND create_month<="%s" AND product_type_id = %d GROUP BY film_type_id';
        $sql = sprintf($sql, $start_date, $end_date, $type);
        $saleTotal1 = DB::select($sql);

        $data = [];

        if (!empty($saleTotal1)) {
            foreach ($saleTotal1 as $item) {
                if (isset($data[$item->simplified_chiness_value])) {
                    $data[$item->simplified_chiness_value] += $item->total;
                } else {
                    $data[$item->simplified_chiness_value] = $item->total;
                }
            }
        }

        $result = [
            'data' => $data,
            'title' => $type == 1 ? '今年|累计漆膜销售卷数' : '今年|累计窗膜销售卷数',
        ];

        return view('admin.stat_compare.total_sale_roll', $result);
    }

    public function totalSaleDealerRoll(Request $request)
    {
        $type = $request->input('type');

        $start_date = date('Y') . '-01';
        $end_date = date('Y-m-d');
        $sql = 'SELECT SUM(num) AS total, dealer_id FROM xpel_dealer_roll_stat  WHERE create_month>="%s" AND create_month<="%s" AND product_type_id=%d AND dealer_id!=dist_id GROUP BY dealer_id ORDER BY total DESC LIMIT 1';
        $sql = sprintf($sql, $start_date, $end_date, $type);
        $saleTotal1 = DB::select($sql);
        $dealer_id = !empty($saleTotal1) && !empty($saleTotal1[0]) && !empty($saleTotal1[0]->dealer_id) ? $saleTotal1[0]->dealer_id : 0;

        if (!empty($dealer_id)) {
            $sql = 'SELECT SUM(num) AS total, film_name as simplified_chiness_value, product_type_id FROM xpel_dealer_roll_stat WHERE create_month>="%s" AND create_month<="%s" AND product_type_id = %d AND dealer_id=%d GROUP BY film_type_id';
            $sql = sprintf($sql, $start_date, $end_date, $type, $dealer_id);
            $result = DB::select($sql);
        }

        $data = [];

        if (!empty($result)) {
            foreach ($result as $item) {
                if (isset($data[$item->simplified_chiness_value])) {
                    $data[$item->simplified_chiness_value] += $item->total;
                } else {
                    $data[$item->simplified_chiness_value] = $item->total;
                }
            }
        }

        $type = $type == 1 ? '漆膜' : '窗膜';

        $result = [
            'data' => $data,
            'title' =>  $type == 1 ? '今年|最佳销售门店漆面销售卷数' : '今年|最佳销售门店窗膜销售卷数',
        ];

        return view('admin.stat_compare.total_sale_roll', $result);
    }

    public function totalSaleDistRoll(Request $request)
    {
        $type = $request->input('type');

        $start_date = date('Y') . '-01';
        $end_date = date('Y-m-d');
        $sql = 'SELECT SUM(num) AS total, dist_id FROM xpel_dealer_roll_stat  WHERE create_month>="%s" AND create_month<="%s" AND product_type_id=%d GROUP BY dist_id ORDER BY total DESC LIMIT 1';
        $sql = sprintf($sql, $start_date, $end_date, $type);
        $saleTotal1 = DB::select($sql);
        $dist_id = !empty($saleTotal1) && !empty($saleTotal1[0]) && !empty($saleTotal1[0]->dist_id) ? $saleTotal1[0]->dist_id : 0;

        if (!empty($dist_id)) {
            $sql = 'SELECT SUM(num) AS total, film_name as simplified_chiness_value, product_type_id FROM xpel_dealer_roll_stat WHERE create_month>="%s" AND create_month<="%s" AND product_type_id = %d AND dist_id=%d GROUP BY film_type_id';
            $sql = sprintf($sql, $start_date, $end_date, $type, $dist_id);
            $result = DB::select($sql);
        }

        $data = [];

        if (!empty($result)) {
            foreach ($result as $item) {
                if (isset($data[$item->simplified_chiness_value])) {
                    $data[$item->simplified_chiness_value] += $item->total;
                } else {
                    $data[$item->simplified_chiness_value] = $item->total;
                }
            }
        }

        $result = [
            'data' => $data,
            'title' =>  $type == 1 ? '今年|最佳销售省代漆面销售卷数' : '今年|最佳销售省代窗膜销售卷数',
        ];

        return view('admin.stat_compare.total_sale_roll', $result);
    }

    public function allSales()
    {
        $type = $this->request['type'];
        $result = $this->statCompareService->allSales($type);

        $result = [
            'data'  => $result,
            'title' => $type == 1 ? '今年|累计漆膜销售卷数' : '今年|累计窗膜销售卷数',
        ];

        return view('admin.stat_compare.all_sales', $result);
    }

    public function allScores()
    {
        $type = $this->request['type'];
        $result = $this->statCompareService->allScores($type);

        $result = [
            'data'  => $result,
            'title' => $type == 1 ? '今年|累计漆膜销售卷数' : '今年|累计窗膜销售卷数',
        ];

        return view('admin.stat_compare.all_scores', $result);
    }

    public function distSales()
    {
        $type = $this->request['type'];
        $result = $this->statCompareService->distSales($type);

        $result = [
            'data'  => $result['list'] ?? [],
            'title' => $type == 1 ? '今年|最佳省代漆膜销售卷数' : '今年|最佳省代窗膜销售卷数',
            'abbr'  => $result['abbr'] ?? '',
        ];

        return view('admin.stat_compare.all_sales', $result);
    }

    public function distScores()
    {
        $type = $this->request['type'];
        $result = $this->statCompareService->distScores($type);

        $result = [
            'data'  => $result['list'] ?? [],
            'title' => $type == 1 ? '今年|最佳省代漆膜任务分数' : '今年|最佳省代窗膜任务分数',
            'abbr'  => $result['abbr'] ?? '',
        ];

        return view('admin.stat_compare.dist_scores', $result);
    }

    public function dealerSales()
    {
        $type = $this->request['type'];
        $result = $this->statCompareService->dealerSales($type);

        $result = [
            'data'  => $result['list'] ?? [],
            'title' => $type == 1 ? '今年|最佳经销商漆膜销售卷数' : '今年|最佳经销商窗膜销售卷数',
            'abbr'  => $result['abbr'] ?? '',
        ];

        return view('admin.stat_compare.all_sales', $result);
    }

    public function dealerScores()
    {
        $type = $this->request['type'];
        $result = $this->statCompareService->dealerScores($type);

        $result = [
            'data'  => $result['list'] ?? [],
            'title' => $type == 1 ? '今年|最佳经销商漆膜任务分数' : '今年|最佳经销商窗膜任务分数',
            'abbr'  => $result['abbr'] ?? '',
        ];

        return view('admin.stat_compare.all_scores', $result);
    }

    public function brandTotal()
    {
        $result = $this->statCompareService->brandTotal();

        $result = [
            'data'  => $result ?? [],
            'title' => '今年|累计车辆品牌',
        ];

        return view('admin.stat_compare.brand', $result);
    }

    public function mostBrand()
    {
        $result = $this->statCompareService->mostBrand();

        $result = [
            'data'  => $result['list'] ?? [],
            'abbr'  => $result['abbr'] ?? '',
            'title' => '今年|累计最多品牌',
        ];

        return view('admin.stat_compare.most_brand', $result);
    }

    public function mostModel()
    {
        $result = $this->statCompareService->mostModel();

        $result = [
            'data'  => $result['list'] ?? [],
            'abbr'  => $result['abbr'] ?? '',
            'title' => '今年|累计最多型号',
        ];

        return view('admin.stat_compare.most_brand', $result);
    }

    public function warrantyTotal()
    {
        $result = $this->statCompareService->warrantyTotal();

        $result = [
            'data'  => $result ?? [],
            'title' => '今年|累计质保数量'
        ];

        return view('admin.stat_compare.warranty_total', $result);
    }

    public function warrantyNum()
    {
        $type = $this->request['type'];

        $result = $this->statCompareService->warrantyNum($type);

        $result = [
            'data'  => $result ?? [],
            'title' => $type == 1 ? '今年|累计漆膜质保数量' : '今年|累计窗膜质保数量',
        ];

        return view('admin.stat_compare.warranty_total', $result);
    }

    public function warrantyRoll()
    {
        $film_type_id = $this->request['film_type_id'];

        $result = $this->statCompareService->warrantyRoll($film_type_id);
        $result = [
            'data'  => $result['list'] ?? [],
            'abbr'  => $result['abbr'] ?? '',
            'title' => '今年|最佳单品质保数量'
        ];

        return view('admin.stat_compare.warranty_total', $result);
    }

    public function sales()
    {
        $type = $this->request->input('type');
        $user_type = $this->request->input('user_type');

        $result = $this->statCompareService->sales($type, $user_type);

        $title = '';
        if($user_type == 1) {
            $title = $type == 1 ? '今年|累计漆膜销售卷数' : '今年|累计窗膜销售卷数';
        }
        if($user_type == 2) {
            $title = $type == 1 ? '今年|累计省代漆膜销售卷数' : '今年|累计省代窗膜销售卷数';
        }
        if($user_type == 3) {
            $title = $type == 1 ? '今年|累计经销商漆膜销售卷数' : '今年|累计经销商窗膜销售卷数';
        }

        $result = [
            'data'  => $result ?? [],
            'title' => $title,
        ];

        return view('admin.stat_compare.sales', $result);
    }

    public function transfer()
    {
        $type = $this->request->input('type');
        $user_type = $this->request->input('user_type');

        $result = $this->statCompareService->transfer($type, $user_type);

        $title = '';
        if($user_type == 1) {
            $title = $type == 1 ? '今年|累计漆膜调货卷数' : '今年|累计窗膜调货卷数';
        }
        if($user_type == 2) {
            $title = $type == 1 ? '今年|累计省代漆膜调货卷数' : '今年|累计省代窗膜调货卷数';
        }
        if($user_type == 3) {
            $title = $type == 1 ? '今年|累计经销商漆膜调货卷数' : '今年|累计经销商窗膜调货卷数';
        }

        $result = [
            'data'  => $result ?? [],
            'title' => $title,
        ];

        return view('admin.stat_compare.sales', $result);
    }

    public function return()
    {
        $type = $this->request->input('type');
        $user_type = $this->request->input('user_type');

        $result = $this->statCompareService->return($type, $user_type);

        $title = '';
        if($user_type == 1) {
            $title = $type == 1 ? '今年|累计漆膜退货卷数' : '今年|累计窗膜退货卷数';
        }
        if($user_type == 2) {
            $title = $type == 1 ? '今年|累计省代漆膜退货卷数' : '今年|累计省代窗膜退货卷数';
        }
        if($user_type == 3) {
            $title = $type == 1 ? '今年|累计经销商漆膜退货卷数' : '今年|累计经销商窗膜退货卷数';
        }

        $result = [
            'data'  => $result ?? [],
            'title' => $title,
        ];

        return view('admin.stat_compare.sales', $result);
    }

    public function compareRoll()
    {
        $type = $this->request->input('type');
        $data = DB::table('roll_restlen')
            ->leftJoin('roll_master', 'roll_master.roll_number', 'roll_restlen.roll_number')
            ->leftJoin('film_type_master', 'roll_master.film_type_id', 'film_type_master.id')
            ->where('roll_restlen.user_id', 1)
            ->where("restlen", '>', 0)
            ->whereNull('roll_restlen.deleted_at')
            ->where("film_type_master.product_type_id", $type)
            ->select('roll_restlen.restlen', 'roll_master.length', 'film_type_master.simplified_chiness_value')
            ->get();
        if (empty($data)) {
            return [];
        }

        $result = [];
        foreach ($data as $item) {
            $len = round($item->restlen / $item->length, 2);
            if (!isset($result[$item->simplified_chiness_value])) {
                $result[$item->simplified_chiness_value] = $len;
            } else {
                $result[$item->simplified_chiness_value] += $len;
            }
        }
        arsort($result);

        $title = $type == 1 ? '今年|累计经国代漆膜库存卷数' : '今年|累计国代窗膜库存卷数';

        $result = [
            'data'  => $result ?? [],
            'title' => $title,
        ];

        return view('admin.stat_compare.country', $result);
    }

    public function compareAbbrRoll()
    {
        $type = $this->request->input('type');
        $data = DB::table('roll_restlen')
            ->leftJoin('roll_master', 'roll_master.roll_number', 'roll_restlen.roll_number')
            ->leftJoin('film_type_master', 'roll_master.film_type_id', 'film_type_master.id')
            ->leftJoin('users', 'users.id', 'roll_restlen.user_id')
            ->where("restlen", '>', 0)
            ->where("users.user_type", $this->request->input('user_type'))
            ->where("film_type_master.product_type_id", $type)
            ->whereNull('roll_restlen.deleted_at')
            ->whereNull('users.deleted_at')
            ->where('users.status', 1)
            ->select('roll_restlen.restlen', 'roll_master.length', 'users.abbr')
            ->get();

        if (empty($data)) {
            return [];
        }

        $result = [];
        foreach ($data as $item) {
            $len = round($item->restlen / $item->length, 2);
            if (!isset($result[$item->abbr])) {
                $result[$item->abbr] = $len;
            } else {
                $result[$item->abbr] += $len;
            }
        }

        if ($this->request->input('user_type') == 2) {
            $title = $type == 1 ? '今年|累计省代漆膜库存卷数' : '今年|累计省代窗膜库存卷数';
        } else {
            $title = $type == 1 ? '今年|累计经销商漆膜库存卷数' : '今年|累计经销商窗膜库存卷数';
        }

        arsort($result);

        $result = [
            'data'  => $result ?? [],
            'title' => $title,
        ];

        return view('admin.stat_compare.abbr', $result);
    }

    public function comparePrice()
    {
        $dist_id = $this->request->get('dist_id') ?? 0;
        $year = $this->request->get('year') ?? date('Y');
        $month = $this->request->get('month');
        $mdtype = $this->request->get('mdtype') ?? 'all';
        $zsdj = $this->request->get('zsdj') ?? 'all';
        $cert_status = $this->request->get('cert_status') ?? '';
        if (!empty($month)) {
            $start_date = date('Y-m-01', strtotime($year . '-' . $month));
            $end_date = date('Y-m-01', strtotime($start_date . ' +1 month'));
        } else {
            $start_date = $year . '-01';
            $end_date = ($year + 1) . '-01';
        }

        $sql = 'SELECT
    dealer.*,
	u.*,
	pm.simplified_chiness_value AS province_name
FROM
    users AS u 
	LEFT JOIN dealer ON u.id = dealer.user_id
	LEFT JOIN province_master AS pm ON pm.id = u.province_id   
WHERE
	u.user_type = 3 AND u.status=1
	AND u.creator_id NOT IN (1515,1554,2784,2803,2815,2823,2835)
	';
        $sql = sprintf($sql, $start_date, $end_date);
        if (!empty($dist_id)) {
            $sql .=' AND u.creator_id=' . $dist_id;
        }
        if ($zsdj !== 'all') {
            $sql .= ' AND u.zsdj=' . $zsdj;
        } else {
            $sql .= ' AND u.zsdj in (3, 5)';
        }
        if ($mdtype !== 'all') {
            $sql .= ' AND u.mdtype=' . $mdtype;
        }
        if (!empty($cert_status)) {
            if ($cert_status == 1) {
                $sql .= ' AND dealer.cert_status !=2';
            }
            if ($cert_status == 2) {
                $sql .= ' AND dealer.cert_status=' . $cert_status;
            }
        }
        $sql .= ' GROUP BY u.id';
        $result = DB::select($sql);
        $data = [];
        if (!empty($result)) {
            $dealerIdArr = [];
            foreach ($result as $item) {
                if (!empty($item->id)) {
                    $dealerIdArr[] = $item->id;
                }
            }

            if (!empty($result)) {
                foreach ($result as &$item) {
                    if (empty($item->task_value) && !empty($item->mdtype)) {
                        if (in_array($item->mdtype, [1, 3, 4]) && !empty($month)) {
                            $item->task_value = 15;
                        }
                        if (in_array($item->mdtype, [1, 3, 4]) && empty($month)) {
                            $item->task_value = $year !== date('Y') ? 15 * 12 : ((int)date('m')) * 15;
                        }
                        if ($item->mdtype == 2 && !empty($month)) {
                            $item->task_value = 4;
                        }
                        if (in_array($item->mdtype, [1, 3, 4]) && empty($month)) {
                            $item->task_value = $year !== date('Y') ? 4 * 12 : ((int)date('m')) * 4;
                        }
                    }
                    if (!empty($item->task_value) && empty($month)) {
                        $item->task_value = $year !== date('Y') ? $item->task_value * 12 : ((int)date('m')) * $item->task_value;
                    }
                }
            }

            $sql = 'SELECT
	SUM(num) AS max_num,
       dealer_id FROM
	xpel_dealer_roll_stat 
WHERE
	create_month>="%s" AND create_month<"%s"
    AND dealer_id IN (%s)
    AND film_type_id=65
    GROUP BY dealer_id
	';
            $sql = sprintf($sql, $start_date, $end_date, implode(',', $dealerIdArr));
            $dealerResult = DB::select($sql);
            if (!empty($dealerResult)) {
                foreach ($result as &$item) {
                    foreach ($dealerResult as $value) {
                        if ($item->id == $value->dealer_id) {
                            $item->max_num = $value->max_num;
                        }
                    }
                }
            }

            $sql = 'SELECT
	SUM(score) AS total_price,
       dealer_id FROM
	xpel_warranty_roll_detail_stat 
WHERE
	create_date>="%s" AND create_date<"%s"
    AND dealer_id IN (%s)
    GROUP BY dealer_id
	';
            $sql = sprintf($sql, $start_date, $end_date, implode(',', $dealerIdArr));
            $warrantyResult = DB::select($sql);
            if (!empty($warrantyResult)) {
                foreach ($result as &$item) {
                    foreach ($warrantyResult as $value) {
                        if ($item->id == $value->dealer_id) {
                            $item->total = $value->total_price;
                        }
                    }
                }
            }


            $sql = 'SELECT
       COUNT(DISTINCT(warranty.id)) AS total,
       warranty.user_id as dealer_id FROM
	warranty_to_vehicle_coverage 
    LEFT JOIN warranty ON warranty_to_vehicle_coverage.warranty_id=warranty.id
WHERE
	warranty_to_vehicle_coverage.created_at>="%s" AND warranty_to_vehicle_coverage.created_at<"%s"
    AND warranty.user_id IN (%s)
    AND part_id IN (150, 152, 153)
    AND warranty.deleted_at IS NULL
    GROUP BY warranty.user_id
	';

            $sql = sprintf($sql, $start_date, $end_date, implode(',', $dealerIdArr));
            $warrantyCarResult = DB::select($sql);
            if (!empty($warrantyCarResult)) {
                foreach ($result as &$item) {
                    foreach ($warrantyCarResult as $value) {
                        if ($item->id == $value->dealer_id) {
                            $item->car_total = $value->total;
                        }
                    }
                }
            }
        }

        $sql = 'SELECT id, abbr FROM users WHERE status=1 AND user_type=2 AND id NOT IN(1515,1554,2784,2803,2815,2823,2835) AND deleted_at IS NULL ORDER BY status, unique_id ASC';
        $distributions = DB::select($sql);

        $months =  [
            '一月','二月','三月','四月','五月','六月','七月','八月','九月','十月','十一月','十二月'
        ];
        $data = [
            'months'        => $months,
            'distributions' => $distributions,
            'list' => $result,
            'start_year' => date('Y'),
            'year' => $year,
            'dist_id' => $dist_id,
            'month' => $month,
            'mdtype' => $mdtype,
            'zsdj'   => $zsdj,
            'cert_status' => $cert_status,
        ];
        return view('admin.stat_compare.dealer_price', $data);
    }

    public function export()
    {
        $dist_id = $this->request->get('dist_id') ?? 0;
        $year = $this->request->get('year') ?? date('Y');
        $month = $this->request->get('month');
        $mdtype = $this->request->get('mdtype') ?? 'all';
        $zsdj = $this->request->get('zsdj') ?? 'all';
        $cert_status = $this->request->get('cert_status') ?? '';

        if (!empty($month)) {
            $start_date = date('Y-m-01', strtotime($year . '-' . $month));
            $end_date = date('Y-m-01', strtotime($start_date . ' +1 month'));
        } else {
            $start_date = $year . '-01';
            $end_date = ($year + 1) . '-01';
        }

        $sql = 'SELECT
    dealer.*,
	u.*,
	pm.simplified_chiness_value AS province_name
FROM
    users AS u 
	LEFT JOIN dealer ON u.id = dealer.user_id
	LEFT JOIN province_master AS pm ON pm.id = u.province_id   
WHERE
	u.user_type = 3 AND u.status=1
	AND u.creator_id NOT IN (1515,1554,2784,2803,2815,2823,2835)
	';
        $sql = sprintf($sql, $start_date, $end_date);
        if (!empty($dist_id)) {
            $sql .=' AND u.creator_id=' . $dist_id;
        }
        if ($zsdj !== 'all') {
            $sql .= ' AND u.zsdj=' . $zsdj;
        } else {
            $sql .= ' AND u.zsdj in (3, 5)';
        }
        if ($mdtype !== 'all') {
            $sql .= ' AND u.mdtype=' . $mdtype;
        }
        if (!empty($cert_status)) {
            if ($cert_status == 1) {
                $sql .= ' AND dealer.cert_status !=2';
            }
            if ($cert_status == 2) {
                $sql .= ' AND dealer.cert_status=' . $cert_status;
            }
        }
        $sql .= ' GROUP BY u.id';
        $result = DB::select($sql);
        $data = [];
        if (!empty($result)) {
            $dealerIdArr = [];
            foreach ($result as $item) {
                if (!empty($item->id)) {
                    $dealerIdArr[] = $item->id;
                }
            }

            $sql = 'SELECT
	SUM(num) AS max_num,
       dealer_id FROM
	xpel_dealer_roll_stat 
WHERE
	create_month>="%s" AND create_month<"%s"
    AND dealer_id IN (%s)
    AND film_type_id=65
    GROUP BY dealer_id
	';
            $sql = sprintf($sql, $start_date, $end_date, implode(',', $dealerIdArr));
            $dealerResult = DB::select($sql);

            $sql = 'SELECT
	SUM(score) AS total_price,
       dealer_id FROM
	xpel_warranty_roll_detail_stat 
WHERE
	create_date>="%s" AND create_date<"%s"
    AND dealer_id IN (%s)
    GROUP BY dealer_id
	';
            $sql = sprintf($sql, $start_date, $end_date, implode(',', $dealerIdArr));
            $warrantyResult = DB::select($sql);
            /*if (!empty($warrantyResult)) {
                foreach ($result as &$item) {
                    foreach ($warrantyResult as $value) {
                        if ($item->id == $value->dealer_id) {
                            $item->total = $value->total_price;
                        }
                    }
                }
            }*/
            $sql = 'SELECT
       COUNT(DISTINCT(warranty.id)) AS total,
       warranty.user_id as dealer_id FROM
	warranty_to_vehicle_coverage 
    LEFT JOIN warranty ON warranty_to_vehicle_coverage.warranty_id=warranty.id
WHERE
	warranty_to_vehicle_coverage.created_at>="%s" AND warranty_to_vehicle_coverage.created_at<"%s"
    AND warranty.user_id IN (%s)
    AND part_id IN (150, 152, 153)
    AND warranty.deleted_at IS NULL
    GROUP BY warranty.user_id
	';

            $sql = sprintf($sql, $start_date, $end_date, implode(',', $dealerIdArr));
            $warrantyCarResult = DB::select($sql);
        }



        $response = [];
        foreach ($result as $item) {
            $data = [
                'province_name' => $item->province_name,
                'abbr' => $item->abbr,
                'zsdj' => $item->zsdj,
                'mdtype' => $item->mdtype,
                'first_name' => $item->first_name,
                'cert_date' => $item->cert_date,
                'extension' => $item->extension,
                'phone_number' => $item->phone_number,
                'cert_price' => $item->cert_price,
                'cert_status' => $item->cert_status,
                'max_num'    => 0,
            ];
            if (!empty($warrantyResult)) {
                foreach ($warrantyResult as $warranty) {
                    if ($item->id == $warranty->dealer_id) {
                        $data['total'] = $warranty->total_price ?? 0;
                    }
                }
            }
            if (!empty($dealerResult)) {
                foreach ($dealerResult as $dealerData) {
                    if ($item->id == $dealerData->dealer_id) {
                        $data['max_num'] = $dealerData->max_num ?? 0;
                    }
                }
            }
            if (!empty($warrantyCarResult)) {
                foreach ($warrantyCarResult as $warrantyCar) {
                    if ($item->id == $warrantyCar->dealer_id) {
                        $data['car_total'] = $warrantyCar->total;
                    }
                }
            }
            if (empty($item->task_value) && !empty($item->mdtype)) {
                if (in_array($item->mdtype, [1, 3, 4]) && !empty($month)) {
                    $data['task_value'] = 15;
                }
                if (in_array($item->mdtype, [1, 3, 4]) && empty($month)) {
                    $data['task_value'] = $year !== date('Y') ? 15 * 12 : ((int)date('m')) * 15;
                }
                if ($item->mdtype == 2 && !empty($month)) {
                    $data['task_value'] = 4;
                }
                if (in_array($item->mdtype, [1, 3, 4]) && empty($month)) {
                    $data['task_value'] = $year !== date('Y') ? 4 * 12 : ((int)date('m')) * 4;
                }
            }
            if (!empty($item->task_value) && empty($month)) {
                $data['task_value'] = $year !== date('Y') ? $item->task_value * 12 : ((int)date('m')) * $item->task_value;
            }
            if (!empty($item->task_value) && !empty($month)) {
                $data['task_value'] = $item->task_value;
            }
            $response[] = $data;
        }

        $uploads = [];
        $fields = [
            'province_name' => '省',
            'abbr' => '店面名称',
            'first_name' => '联系人姓名',
            'ext_phone' => '电话号码',
            'mdtype_name' => '经销商级别',
            'cert_date' => '认证日期',
            'cert_status' => '认证状态',
            'zsdj_name' => '钻石等级',
            'cert_price' => '认证费用',
            'max_num'   => 'MAX卷数',
            'car_total' => '全车数量',
            'task_value' => '任务值',
            'total' => '质保分数',
        ];
        if (!empty($month)) {
            $fields['is_return'] = '是否返点';
        }
        foreach ($response as $k => $v) {
            /*if (!empty($v->mdtype)) {
                $v->mdtype_name = ts('mdtype'. $v->mdtype);
            } else {
                $v->mdtype_name = '';
            }
            $v->zsdj_name = ts('zsdj' . $v->zsdj);
            $v->ext_phone = $v->extension . '-' . $v->phone_number;
            $v->task_value = ($v->qm_score ?: 0) + ($v->win_score ?: 0);*/
            $item = [];
            foreach ($fields as $f=>$m) {
                if ($f === 'ext_phone') {
                    $item[] = [
                        'label' => $m,
                        'value' => ($v['extension'] ?? ''). '-' . ($v['phone_number'] ?? '')
                    ];
                    continue;
                }
                if ($f === 'cert_status') {
                    $item[] = [
                        'label' => $m,
                        'value' => !empty($v['cert_status']) ? ($v['cert_status'] == 1 ? '未完成' : '已完成') : '',
                    ];
                    continue;
                }
                if (!empty($month) && $f === 'is_return') {
                    $item[] = [
                        'label' => $m,
                        'value' => !empty($month) ? (!empty($v['car_total']) && $v['car_total'] >= $v['task_value'] ? '是' : '否') : '',
                    ];
                    continue;
                }
                if ($f === 'zsdj_name') {
                    $item[] = [
                        'label' => $m,
                        'value' => !empty($v['zsdj']) ? ts('zsdj' . $v['zsdj']) : ''
                    ];
                    continue;
                }
                if ($f === 'mdtype_name') {
                    $item[] = [
                        'label' => $m,
                        'value' => !empty($v['mdtype']) ? ts('mdtype'. $v['mdtype']) : ''
                    ];
                    continue;
                }
                if ($f === 'total') {
                    $item[] = [
                        'label' => $m,
                        'value' => !empty($v['total']) ? $v['total'] : 0,
                    ];
                    continue;
                }
                if ($f === 'province_name') {
                    $item[] = [
                        'label' => $m,
                        'value' => !empty($v['province_name']) ? $v['province_name'] : '',
                    ];
                    continue;
                }
                if ($f === 'abbr') {
                    $item[] = [
                        'label' => $m,
                        'value' => !empty($v['abbr']) ? $v['abbr'] : '',
                    ];
                    continue;
                }
                if ($f === 'first_name') {
                    $item[] = [
                        'label' => $m,
                        'value' => !empty($v['first_name']) ? $v['first_name'] : '',
                    ];
                    continue;
                }
                if ($f === 'cert_price') {
                    $item[] = [
                        'label' => $m,
                        'value' => !empty($v['cert_price']) ? $v['cert_price'] : '',
                    ];
                    continue;
                }
                if ($f === 'cert_date') {
                    $item[] = [
                        'label' => $m,
                        'value' => !empty($v['cert_date']) ? $v['cert_date'] : '',
                    ];
                    continue;
                }
                $item[] = [
                    'label' => $m,
                    'value' => $v[$f] ?? 0
                ];
            }
            $uploads[] = $item;
        }
        return makeExcel($uploads, '门店质保销售统计');
    }
}
